// @ts-ignore
import {DatabaseSync} from 'node:sqlite'

class User {
    id: number | null
    username: string
    password: string

    constructor(id: number | null = null, username: string, password: string) {
        this.id = id
        this.username = username
        this.password = password
    }
}

class Page<T> {
    total: number
    page: number
    pageSize: number
    data: Array<T>

    constructor(total: number, page: number, pageSize: number, data: Array<T>) {
        this.total = total
        this.page = page
        this.pageSize = pageSize
        this.data = data
    }
}

const database = new DatabaseSync('sqlite.db')

function initDatabase() {
    if (database.prepare("SELECT * FROM sqlite_master WHERE name='user' LIMIT 1").all().length == 0) {
        database.exec(
            `CREATE TABLE user
             (
                 id       INTEGER PRIMARY KEY AUTOINCREMENT,
                 username TEXT NOT NULL,
                 password TEXT NOT NULL
             );`
        )
    }
}

function getUserById(id: number): User {
    const query = database.prepare('SELECT * FROM user WHERE id=? LIMIT 1')
    const res = query.get(id)
    return res ? res : null

}

function listUsers(): Array<User> {
    const query = database.prepare('SELECT * FROM user')
    return query.all()
}


function listUsersPage(page: number, pageSize: number): Page<User> {
    const offset = (page - 1) * pageSize;
    const query = database.prepare('SELECT COUNT(*) AS total FROM user');
    const totalResult = query.get();
    const totalUsers = totalResult.total;

    const usersQuery = database.prepare('SELECT * FROM user LIMIT ? OFFSET ?');
    const users = usersQuery.all(pageSize, offset);

    return new Page<User>(totalUsers, page, pageSize, users);
}

function saveUser(user: User) {
    if (user.id) {
        const update = database.prepare('UPDATE user SET username=?, password=? WHERE id=?')
        update.run(user.username, user.password, user.id)
        return
    }
    const insert = database.prepare('INSERT INTO user (username, password) VALUES (?, ?)')
    insert.run(user.username, user.password)
}

function deleteUser(id: number) {
    const del = database.prepare('DELETE FROM user WHERE id=?')
    del.run(id)
}

export {initDatabase, getUserById, listUsers, listUsersPage,saveUser, deleteUser, User}

